#include "datahandler.h"
#include <QtSql>
#include <QVector>
#include "aprs_parser.h"

QSqlDatabase db;
QFile file;
QTimer *timer;

DataHandler::DataHandler()
{
    //db = QSqlDatabase::addDatabase("QSQLITE");	//creates connection to SQLite

    timer = new QTimer(this);
    connect(timer, SIGNAL(timeout()), this, SLOT(CheckTimes()));
}

bool DataHandler::ConnectToDB()
{
    if(sqliteType)
    {
        db = QSqlDatabase::addDatabase("QSQLITE");	//creates connection to SQLite
        db.setDatabaseName(dbName);
        file.setFileName(dbName + "_log.txt");
    }
    else
    {
        db = QSqlDatabase::addDatabase("QMYSQL");       //creates connection to MySQL
        db.setDatabaseName(dbName);
        db.setHostName(hostName);
        db.setUserName(userName);
        db.setPassword(password);
        db.setPort(portNumber);
        file.setFileName("..//..//" + dbName + "_log.txt");
    }

    if (!db.open() || !file.open(QIODevice::WriteOnly | QIODevice::Text)) {
        QSqlError error = db.lastError();
        return false;
    }
    else
    {
        QTextStream stream(&file);
        stream << QDateTime::currentDateTime().toString() << endl << "\nCould not parse the following packets: \n" << endl;
        CreateTables();
        Initialize();
        timer->start(5000);
        return true;
    }
}

void DataHandler::Initialize()
{
    if(purgeTime > 0)
    {
        QDateTime checkDate = QDateTime::currentDateTime();
        checkDate = checkDate.toUTC();
        if(purgeUnit == "days")
        {
            checkDate = checkDate.addDays(-purgeTime);
        }
        else //hours
        {
            checkDate = checkDate.addSecs(-purgeTime*3600);
        }

        QSqlQuery query;
        query.prepare("DELETE FROM Position WHERE Time <= :PurgeDate");
        query.bindValue(":PurgeDate", checkDate);

        query.exec();
    }

    QDateTime checkTime = QDateTime::currentDateTime();
    checkTime = checkTime.toUTC();
    if(clearUnit == "hrs")
    {
        checkTime = checkTime.addSecs(-clearTime*3600);
    }
    else //minutes
    {
        checkTime = checkTime.addSecs(-clearTime*60);
    }

    if(clearTime > 0)
    {
        QSqlQuery query;
        query.prepare("UPDATE Position SET Cleared = :true WHERE (Time <= :ClearTime) and (Cleared = :false)");
        query.bindValue(":ClearTime", checkTime);
        query.bindValue(":true", "True");
        query.bindValue(":false", "False");
        query.exec();
    }

    QVector<QString> callsigns;
    QVector<int> symbolTables;
    QVector<int> symbolCodes;
    QVector<QString> lats;
    QVector<QString> lons;
    QSqlQuery query;
    query.prepare("SELECT SourceAddr, SymbolTable, SymbolCode, Lat, Lon FROM Position WHERE (Cleared = :cleared)");
    query.bindValue(":cleared", "False");
    query.exec();

    while(query.next())
    {
        callsigns.append(query.value(0).toString());
        symbolTables.append(query.value(1).toInt());
        symbolCodes.append(query.value(2).toInt());
        lats.append(query.value(3).toString());
        lons.append(query.value(4).toString());
    }

    for(int i = 0; i < callsigns.size(); i++)
    {
        if(lats[i] != "nan" && lons[i] != "nan")
        {
            emit StationPlot(callsigns[i], char(symbolTables[i]), char(symbolCodes[i]), lats[i].toFloat(), lons[i].toFloat());
        }
    }
}

void DataHandler::CreateTables()
{
    QSqlQuery query;
    query.prepare("SELECT * FROM Position");
    if(!query.exec())
    {
        query.exec("create table Position (Unparsed TEXT, Payload TEXT, DestAddr TEXT, SourceAddr TEXT, Digipeaters TEXT, "
                      "Time DATETIME, Lat TEXT, Lon TEXT, Course TEXT, Speed TEXT, RadioRange TEXT, Alt TEXT, SymbolTable TEXT, SymbolCode TEXT, Telemetry TEXT, Status TEXT, ObjectName TEXT, ItemName TEXT, PHG TEXT, "
                      "Storm TEXT, Bearing TEXT, Number TEXT, Range TEXT, Quality TEXT, AreaObject TEXT, WindDir TEXT, WindSpeed TEXT, WindGust TEXT, Temp TEXT, RainHr TEXT, Rain24Hr TEXT, RainMid TEXT, Humidity TEXT, Pressure TEXT, Cleared TEXT)");

        //**FOR TESTING**
//        QDateTime dt1 = QDateTime::currentDateTime();
//        dt1 = dt1.addDays(-3);
//        QDateTime dt2 = QDateTime::currentDateTime();
//        dt2 = dt2.addDays(-1);
//        QDateTime dt3 = QDateTime::currentDateTime();
//        dt3 = dt3.addDays(-1);
//        QDateTime dt4 = QDateTime::currentDateTime();
//        dt4 = dt4.addSecs(-3595);
//        QDateTime dt5 = QDateTime::currentDateTime();
//        query.prepare("INSERT INTO Position (SourceAddr, Time) VALUES (:test1, :date)");
//        query.bindValue(":date", dt1);
//        query.bindValue(":test1", "test1");
//        if(!query.exec())
//            qDebug() << "insert failed";
//        query.prepare("INSERT INTO Position (SourceAddr, Time, Cleared) VALUES (:test2, :date, :false)");
//        query.bindValue(":date", dt2);
//        query.bindValue(":test2", "test2");
//        query.bindValue(":false", "False");
//        query.exec();
//        query.prepare("INSERT INTO Position (SourceAddr, Time, Cleared) VALUES (:test3, :date, :false)");
//        query.bindValue(":date", dt3);
//        query.bindValue(":test3", "test3");
//        query.bindValue(":false", "False");
//        query.exec();
//        query.prepare("INSERT INTO Position (SourceAddr, Time, Cleared) VALUES (:test4, :date, :false)");
//        query.bindValue(":date", dt4);
//        query.bindValue(":test4", "test4");
//        query.bindValue(":false", "False");
//        if(!query.exec())
//            qDebug() << "insert4 failed";
//        query.prepare("INSERT INTO Position (SourceAddr, Time, Cleared) VALUES (:test5, :date, :false)");
//        query.bindValue(":date", dt5);
//        query.bindValue(":test5", "current");
//        query.bindValue(":false", "False");
//        query.exec();
        //****
    }
}

void DataHandler::StoreData(QString packet)
{
    APRSParser *parser = new APRSParser();
    parser->year = 0; parser->month = 0; parser->day = 0; parser->hour = 0; parser->minute = 0; parser->second = 0;
    enum result res = parser->parse(packet);
    if(res != OK)
    {
        if(packet[0] != '#')
        {
            QTextStream stream(&file);
            stream << packet << endl;
        }
        return;
    }

    if(!DuplicatePacket(parser))
    {
        //Setup variables
        QString digipeaters = "";
        for(int i = 0; i < parser->digipeaters.length(); i++)
        {
            digipeaters = digipeaters + parser->digipeaters[i] + ", ";
        }

        QDateTime time;
        time = time.currentDateTime();
        time = time.toUTC();
//        int year = (parser->year != 0) ? parser->year : time.date().year();
//        int month = (parser->month != 0) ? parser->month : time.date().month();
//        int day = (parser->day != 0) ? parser->day : time.date().day();
//        int hour = (parser->hour != 0) ? parser->hour : time.time().hour();
//        int minute = (parser->minute != 0) ? parser->minute : time.time().minute();
//        int second = (parser->second != 0) ? parser->second : time.time().second();
//        QDate *_date = new QDate(year, month, day);
//        QTime *_time = new QTime(hour, minute, second);
        //if(_date->isValid() && _time->isValid())
        //{
//            time.setDate(*_date);
//            time.setTime(*_time);
        //}

        QString _telemetry = "NAN";
        QString _status = "NAN";
        QString _objectName = "NAN";
        QString _itemName = "NAN";

        if(parser->type == T_TELEMETRY)
        {
            _telemetry = parser->pktbody;
        }

        else if(parser->type == T_STATUS)
        {
            _status = parser->data;
        }

        else if(parser->type == T_OBJECT)
        {
            _objectName = parser->data;
        }

        else if(parser->type == T_ITEM)
        {
            _itemName = parser->data;
        }

        //Check if callsign already exists and needs to be updated
        QSqlQuery query;
        query.prepare("SELECT * FROM Position WHERE SourceAddr = :callsign");
        query.bindValue(":callsign", parser->srccallsign);
        query.exec();

        if(query.next() && (parser->type != T_ITEM && parser->type != T_OBJECT && parser->type != T_MESSAGE))
        {
            if(parser->type == T_LOCATION)
            {
                query.prepare("UPDATE Position SET (Time = :time, Lat = :lat, Lon = :lon, Speed = :speed, Course = :course, Cleared = :cleared) WHERE (SourceAddr LIKE :callsign)");
                query.bindValue(":callsign", parser->srccallsign);
                query.bindValue(":time", time);
                query.bindValue(":lat", parser->latitude);
                query.bindValue(":lon", parser->longitude);
                query.bindValue(":speed", parser->speed);
                query.bindValue(":course", parser->course);
                query.bindValue(":cleared", "False");
                query.exec();

                emit StationPlot(parser->srccallsign, parser->symboltable, parser->symbolcode, parser->latitude, parser->longitude);
            }
            else if(parser->type == T_STATUS)
            {
                query.prepare("UPDATE Position SET (Time = :time, Status = :status) WHERE (SourceAddr LIKE :callsign)");
                query.bindValue(":callsign", parser->srccallsign);
                query.bindValue(":time", time);
                query.bindValue(":status", _status);
                query.exec();
            }
            else if(parser->type == T_TELEMETRY)
            {
                query.prepare("UPDATE Position SET (Time = :time, Telemetry = :telemetry) WHERE (SourceAddr LIKE :callsign)");
                query.bindValue(":callsign", parser->srccallsign);
                query.bindValue(":time", time);
                query.bindValue(":telemetry", _telemetry);
                query.exec();
            }
            else if(parser->type == T_WX)
            {
                query.prepare("UPDATE Position SET (Time = :time, WindDir = :winddir, WindSpeed = :windspeed, WindGust = :windgust, Temp = :temp, RainHr = :rainhr, Rain24Hr = :rain24hr, RainMid = :rainmid, Humidity = :humidity, Pressure = :pressure) WHERE (SourceAddr LIKE :callsign)");
                query.bindValue(":callsign", parser->srccallsign);
                query.bindValue(":time", time);
                query.bindValue(":winddir", parser->wind_direction);
                query.bindValue(":windspeed", parser->wind_speed);
                query.bindValue(":windgust", parser->wind_gust);
                query.bindValue(":temp", parser->temperature);
                query.bindValue(":rainhr", parser->rain_1h);
                query.bindValue(":rain24hr", parser->rain_24h);
                query.bindValue(":rainmid", parser->rain_midnight);
                query.bindValue(":humidity", parser->humidity);
                query.bindValue(":pressure", parser->pressure);
                query.exec();
            }
        }

        else  //New entry needed
        {
            QSqlQuery query;
            query.prepare("INSERT INTO Position VALUES (:Unparsed, :Payload, :DestAddr, :SourceAddr, :Digipeaters, "
                          ":Time, :Lat, :Lon, :Course, :Speed, :RadioRange, :Alt, :SymbolTable, :SymbolCode, :Telemetry, :Status, :ObjectName, :ItemName, :PHG, "
                          ":Storm, :Bearing, :Number, :Range, :Quality, :AreaObject, :WindDir, :WindSpeed, :WindGust, :Temp, :RainHr, :Rain24Hr, :RainMid, :Humidity, :Pressure, :Cleared)");
            query.bindValue(":Unparsed", packet);
            query.bindValue(":Payload", parser->pktbody);
            query.bindValue(":DestAddr", parser->dstcallsign);
            query.bindValue(":SourceAddr", parser->srccallsign);
            query.bindValue(":Digipeaters", digipeaters);
            query.bindValue(":Time", time);
            query.bindValue(":Lat", parser->latitude);
            query.bindValue(":Lon", parser->longitude);
            query.bindValue(":Course", parser->course);
            query.bindValue(":Speed", parser->speed);
            query.bindValue(":RadioRange", parser->hash.value("radiorange"));
            query.bindValue(":Alt", parser->altitude);
            query.bindValue(":SymbolTable", parser->symboltable);
            query.bindValue(":SymbolCode", parser->symbolcode);
            query.bindValue(":Telemetry", _telemetry);
            query.bindValue(":Status", _status);
            query.bindValue(":ObjectName", _objectName);
            query.bindValue(":ItemName", _itemName);
            query.bindValue(":PHG", parser->phg);
            query.bindValue(":Storm", NAN);  //not in parser
            query.bindValue(":Bearing", NAN);  //not in parser
            query.bindValue(":Number", NAN);  //not in parser
            query.bindValue(":Range", NAN);  //not in parser
            query.bindValue(":Quality", NAN);  //not in parser
            query.bindValue(":AreaObject", NAN);  //not in parser
            query.bindValue(":WindDir", parser->wind_direction);
            query.bindValue(":WindSpeed", parser->wind_speed);
            query.bindValue(":WindGust", parser->wind_gust);
            query.bindValue(":Temp", parser->temperature);
            query.bindValue(":RainHr", parser->rain_1h);
            query.bindValue(":Rain24Hr", parser->rain_24h);
            query.bindValue(":RainMid", parser->rain_midnight);
            query.bindValue(":Humidity", parser->humidity);
            query.bindValue(":Pressure", parser->pressure);
            query.bindValue(":Cleared", "False");
            query.exec();

            //Check if position report
            if(parser->type == T_LOCATION)
            {
                emit StationPlot(parser->srccallsign, parser->symboltable, parser->symbolcode, parser->latitude, parser->longitude);
            }
        }
    }
}

bool DataHandler::DuplicatePacket(APRSParser *parser)
{
    QSqlQuery query;
    query.prepare("SELECT * FROM Position WHERE (SourceAddr = :sourceaddr) and (DestAddr = :destaddr) and (Payload = :payload)");
    query.bindValue(":sourceaddr", parser->srccallsign);
    query.bindValue(":destaddr", parser->dstcallsign);
    query.bindValue(":payload", parser->pktbody);
    query.exec();

    return query.next();
}

void DataHandler::CheckTimes()
{
    if(purgeTime > 0)
    {
        QDateTime checkDate = QDateTime::currentDateTime();
        checkDate = checkDate.toUTC();
        if(purgeUnit == "days")
        {
            checkDate = checkDate.addDays(-purgeTime);
        }
        else //hours
        {
            checkDate = checkDate.addSecs(-purgeTime*3600);
        }

        QSqlQuery query;
        query.prepare("DELETE FROM Position WHERE Time <= :PurgeDate");
        query.bindValue(":PurgeDate", checkDate);

        query.exec();
    }

    if(clearTime > 0)
    {
        QDateTime checkDate = QDateTime::currentDateTime();
        checkDate = checkDate.toUTC();
        if(clearUnit == "hrs")
        {
            checkDate = checkDate.addSecs(-clearTime*3600);
        }
        else //minutes
        {
            checkDate = checkDate.addSecs(-clearTime*60);
        }

        QSqlQuery query;
        query.prepare("SELECT SourceAddr FROM Position WHERE (Time <= :ClearTime) and (Cleared = :cleared)");
        query.bindValue(":ClearTime", checkDate);
        query.bindValue(":cleared", "False");
        query.exec();

        while(query.next())
        {
            emit StationClear(query.value(0).toString());
        }

        query.prepare("UPDATE Position SET Cleared = :true WHERE (Time <= :ClearTime) and (Cleared = :false)");
        query.bindValue(":ClearTime", checkDate);
        query.bindValue(":true", "True");
        query.bindValue(":false", "False");
        query.exec();
    }
}
